<?php
class MyCase{
	
	/**
	*	Esta función es responsable de 
	*	@autor: JSL
	*	@date: 31/01/2012
	*/
	function myCase(){
		
	}
	
	/**
	*	Esta función es responsable de agregar un nuevo caso.
	*	@autor: JSL
	*	@date: 31/01/2012
	*/
	function addCase($data){
		$sql = "INSERT INTO my_case (case_code, cli_code, use_code, bus_code, case_number, case_comment, case_date_create, case_balance, case_approved) VALUES 
			( NULL, ".$data->cli_code.", ".$data->use_code.", ".$data->bus_code.", '".$data->case_number."', '".$data->case_comment."', '".date("Y-m-d H:i:s")."', ".$data->case_balance.", 0);"; 
		
		if ( mysql_query ($sql) )
			return mysql_insert_id();
		else
			return false;
	}
	
	/**
	*	Esta función es responsable de obtener los datos de un caso
	*	@autor: JSL
	*	@date: 31/01/2012
	*/
	function getCase($case_code){
		$sql = "		
		SELECT t1.case_code, t1.cli_code, t1.use_code, t1.bus_code, t1.case_number, t1.case_comment, t1.case_date_create, t1.case_balance, t1.case_approved, t1.bus_name,
			t1.use_name, t1.use_lastname, t1.use_email, t1.use_login, cl.cli_name, cl.cli_lastname, cl.cli_email
		FROM
		(SELECT t.case_code, t.cli_code, t.use_code, t.bus_code, t.case_number, t.case_comment, t.case_date_create, t.case_balance, t.case_approved, t.bus_name, u.use_name, 
			u.use_lastname, u.use_email, 
		u.use_login
		FROM (SELECT cs.case_code, cs.cli_code, cs.use_code, cs.bus_code, cs.case_number, cs.case_comment, cs.case_date_create, cs.case_balance, cs.case_approved ,bt.bus_name
			  FROM business_type bt JOIN my_case cs ON bt.bus_code=cs.bus_code WHERE cs.case_code=".$case_code.") t
		LEFT JOIN user u ON t.use_code=u.use_code) t1
		JOIN client cl ON t1.cli_code=cl.cli_code;";
		
		if ( $result = mysql_query ($sql) ){
			
			if ( $row = mysql_fetch_array ($result) ){
				$data->case_code = $row["case_code"];
				$data->cli_code = $row["cli_code"];
				$data->use_code = $row["use_code"];
				$data->bus_code = $row["bus_code"];
				$data->case_number = $row["case_number"];
				$data->case_comment = $row["case_comment"];
				$data->case_date_create = $row["case_date_create"];
				$data->case_balance = $row["case_balance"];
				$data->case_approved = $row["case_approved"];
				
				$data->bus_name = $row["bus_name"];
				$data->use_name = $row["use_name"];
				$data->use_lastname = $row["use_lastname"];
				$data->use_email = $row["use_email"];
				$data->use_login = $row["use_login"];
				$data->cli_name = $row["cli_name"];
				$data->cli_lastname = $row["cli_lastname"];
				$data->cli_email = $row["cli_email"];
			}
			
			return $data;
		}
		else
			return false;
	}
	
	/**
	*	Esta función es responsable de actualizar caso.
	*	@autor: JSL
	*	@date: 31/01/2012
	*/
	function updateCase($data){
		$sql = "UPDATE my_case SET use_code=".$data->use_code.", bus_code=".$data->bus_code.", case_comment='".$data->case_comment."', case_balance=".$data->case_balance.",
				case_approved=".$data->case_approved." WHERE case_code = ".$data->case_code.";";
		
		if ( mysql_query ($sql) )
			return true;
		else
			die($sql);;
	}
	
	/**
	*	Esta función es responsable de listar los casos.
	*	@autor: JSL
	*	@date: 31/01/2012
	*/
	function listCases($search = '', $order_by = '', $init = 0, $amount = 0, $cli_code = 0){
		$where = '';
		
		if ( $search != '' )
			$where = " AND (cli_name like '%".$search."%' OR cli_lastname like '%".$search."%' OR cli_ss like '%".$search."%' OR case_number like '%".$search."%' OR case_date_create like '%".$search."%' OR bus_name like '%".$search."%' OR use_name like '%".$search."%' OR use_lastname like '%".$search."%' OR case_comment like '%".$search."%') ";
		
		if ( $cli_code != 0 ){
			$where = " AND cli_code=".$cli_code;
		}
		
		$where_user = '';
		if ( $_SESSION['use_type'] == 'Adviser' )
			$where_user = "  AND use_type='Adviser' ";
		
		$sql = "		
		SELECT t1.case_code, t1.cli_code, t1.use_code, t1.bus_code, t1.case_number, t1.case_comment, t1.case_date_create, t1.case_balance, t1.case_approved, t1.bus_name, 
			t1.use_name, t1.use_lastname, t1.use_email, t1.use_login, cl.cli_name, cl.cli_lastname,cl.cli_ss, cl.cli_email
		FROM
		(SELECT t.case_code, t.cli_code, t.use_code, t.bus_code, t.case_number, t.case_comment, t.case_date_create, t.case_balance, t.case_approved, t.bus_name, u.use_name,
			u.use_lastname, u.use_email, u.use_login
		FROM (SELECT cs.case_code, cs.cli_code, cs.use_code, cs.bus_code, cs.case_number, cs.case_comment, cs.case_date_create, cs.case_balance, cs.case_approved, bt.bus_name
			  FROM business_type bt JOIN my_case cs ON bt.bus_code=cs.bus_code) t
		LEFT JOIN user u ON t.use_code=u.use_code ".$where_user.") t1
		JOIN client cl ON t1.cli_code=cl.cli_code WHERE cl.cli_status <> 'Deleted' ".$where." ".$order_by;
		
		if ( ! ( $init == 0 && $amount == 0 ) )
			$sql .= " LIMIT ".$init.", ".$amount.";";
		
		if ( $result = mysql_query ($sql) ){
			$i =  0;
			while ( $row = mysql_fetch_array ($result) ){				
				
				$data[$i]->case_code = $row["case_code"];
				$data[$i]->cli_code = $row["cli_code"];
				$data[$i]->use_code = $row["use_code"];
				$data[$i]->bus_code = $row["bus_code"];
				$data[$i]->cli_ss = $row["cli_ss"];
				$data[$i]->case_number = $row["case_number"];
				$data[$i]->case_comment = $row["case_comment"];
				$data[$i]->case_date_create = $row["case_date_create"];
				$data[$i]->case_balance = $row["case_balance"];
				$data[$i]->case_approved = $row["case_approved"];
				
				$data[$i]->bus_name = $row["bus_name"];
				$data[$i]->use_name = $row["use_name"];
				$data[$i]->use_lastname = $row["use_lastname"];
				$data[$i]->use_email = $row["use_email"];
				$data[$i]->use_login = $row["use_login"];
				$data[$i]->cli_name = $row["cli_name"];
				$data[$i]->cli_lastname = $row["cli_lastname"];
				$data[$i]->cli_email = $row["cli_email"];
				
				$i++;
			}
			
			return $data;
		}
		else
			return false;
	}
	
	/**
	*	Esta función es responsable de contar cuantos casos existen.
	*	@autor: JSL
	*	@date: 31/01/2012
	*/
	function countCases($search = '', $cli_code = 0){
		$where = '';		
		if ( $search != '' )
			$where = " AND (cli_name like '%".$search."%' OR cli_lastname like '%".$search."%' OR cli_ss like '%".$search."%' OR case_number like '%".$search."%' OR case_date_create like '%".$search."%' OR bus_name like '%".$search."%' OR use_name like '%".$search."%' OR use_lastname like '%".$search."%' OR case_comment like '%".$search."%') ";
		
		if ( $cli_code != 0 ){
			$where = " AND cli_code=".$cli_code;
		}
		
		$where_user = '';
		if ( $_SESSION['use_type'] == 'Adviser' )
			$where_user = "  AND use_type='Adviser' ";
		
		$sql = "		
		SELECT COUNT(*) AS cant FROM
		(SELECT t.case_code, t.cli_code, t.use_code, t.bus_code, t.case_number, t.case_comment, t.case_date_create, t.bus_name, u.use_name, u.use_lastname, u.use_email, 
		u.use_login
		FROM (SELECT cs.case_code, cs.cli_code, cs.use_code, cs.bus_code, cs.case_number, cs.case_comment, cs.case_date_create ,bt.bus_name
			  FROM business_type bt JOIN my_case cs ON bt.bus_code=cs.bus_code) t
		LEFT JOIN user u ON t.use_code=u.use_code ".$where_user.") t1
		JOIN client cl ON t1.cli_code=cl.cli_code WHERE cl.cli_status <> 'Deleted' ".$where.";";
		
		if ( $result = mysql_query ($sql) ){
			if ( $row = mysql_fetch_array ($result) )
				return $row["cant"];
			else
				return false;		
		}		
		else
			return false;
	}
	
	/**
	*	Esta función es responsable de listar los tipos de negocio.
	*	@autor: JSL
	*	@date: 31/01/2012
	*/
	function listBusinessType(){
		$sql = "SELECT bt.bus_code, bt.bus_name FROM business_type bt;";
				
		if ( $result = mysql_query ($sql) ){
			$data = array ();
			$i =  0;
			while ( $row = mysql_fetch_array ($result) ){				
				$data[$i]->bus_code = $row["bus_code"];
				$data[$i]->bus_name = $row["bus_name"];
				
				$i++;
			}			
			return $data;
		}
		else
			return false;
	}
	
	/**
	*	Esta función es responsable de listar los clientes.
	*	@autor: JSL
	*	@date: 31/01/2012
	*/
	function listClients(){
		$sql = "SELECT cl.cli_code, cl.cli_name, cl.cli_lastname FROM client cl WHERE cl.cli_status='Active';";
				
		if ( $result = mysql_query ($sql) ){
			$i =  0;
			while ( $row = mysql_fetch_array ($result) ){
				$data[$i]->cli_code = $row["cli_code"];
				$data[$i]->cli_name = $row["cli_name"];
				$data[$i]->cli_lastname = $row["cli_lastname"];
				
				$i++;
			}			
			return $data;
		}
		else
			return false;
	}
	
	/**
	*	Esta función obtiene los numeros de caso de un cliente.
	*	@autor: JSL
	*	@date: 31/01/2012
	*/
	function listCasesClient($cli_code = 0){		
		$sql = "		
		SELECT cs.case_code, cs.case_number, bt.bus_name FROM business_type bt JOIN my_case cs ON bt.bus_code=cs.bus_code WHERE cs.cli_code=".$cli_code;
		
		if ( $result = mysql_query ($sql) ){
			$i =  0;
			while ( $row = mysql_fetch_array ($result) ){				
				
				$data[$i]->case_code = $row["case_code"];
				$data[$i]->case_number = $row["case_number"];
				$data[$i]->bus_name = $row["bus_name"];
				
				$i++;
			}
			
			return $data;
		}
		else
			return false;
	}
	
	/**
	*	Funcion responsable de obtener el siguiente case_number disponible.
	*	@autor: JSL
	*	@date: 31/01/2012
	*/
	function getNextCaseNumber(){
		$sql = "SELECT MAX(cs.case_code)+1 as next_case_number FROM my_case cs;";
		
		if ( $result = mysql_query ($sql) ){
			if ( $row = mysql_fetch_array ($result) ){
				return (int) $row["next_case_number"];
			}
			else
				return false;
		}
		else
			return false;
	}
	
	function getNumberCase(){
	  $sql = "SELECT max(case_code) as albeiro FROM my_case ";
		  if ( $result = mysql_query ($sql) ){
				if ( $row = mysql_fetch_array ($result) ){
					return (int) $row["albeiro"];
				}
				else
					return false;
			}
			else
				return false;
		}

}
?>